﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;

namespace template_dll
{
    public partial class Form3 : Form
    {
        private SqlConnection conn;
        private DataTable dt_rep;
        public string drug_code;
        public string drug_name;
        public string delta;
        public int sign;
        private DataTable tbl_drugs = new DataTable();

        public class cMain
        {
            public static Control Form3Control = null;
            private cMain() { }
        }

        public Form3()
        {
            InitializeComponent();
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            //- Редактирование товаров из списка
            cMain.Form3Control = this;
            this.Text = "Товары";
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[1];
            par[0].name = "@flag_print";
            par[0].value = 1;
            dt_rep = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_PrintPharmDrugs", 60, par);
            dt_rep.Columns[5].ReadOnly = false;
            dataGridView1.DataSource = dt_rep.DefaultView;
            dataGridView1.Columns["Знак дельты"].ReadOnly = false;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //- Сохранить и выйти
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[9];
                par[0].name = "@flag_ins";
                par[0].value = 1;
                par[1].name = "@pharmcode";
                par[1].value = null;
                par[2].name = "@flag_bit";
                par[2].value = null;
                par[3].name = "@drugcode";
                par[3].value = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value);
                par[4].name = "@delta";
                par[4].value = Convert.ToInt32(dataGridView1.Rows[i].Cells[4].Value);
                par[5].name = "@sign";
                par[5].value = Convert.ToInt32(dataGridView1.Rows[i].Cells[5].Value);
                par[6].name = "@flag_new";
                par[6].value = 1;
                par[7].name = "@date_b";
                par[7].value = Convert.ToDateTime(dataGridView1.Rows[i].Cells[6].Value);
                par[8].name = "@date_e";
                par[8].value = Convert.ToDateTime(dataGridView1.Rows[i].Cells[7].Value);

                int rez = 0;
                rez = (new SqlStorProc()).SqlProcExecNonQuery(conn, "[pas_InsPharmDrugs]", 30, par);

                conn.Close();
                this.Close();
            }
            this.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //- Закрыть без сохранения
            this.Close();
        }
        private void button3_Click(object sender, EventArgs e)
        {
            //- Загрузить из Excel файла
            try
            {
                dataGridView1.DataSource = null;
                OpenFileDialog opf = new OpenFileDialog();
                opf.Filter = "Файлы Excel (1997-2007)|*.XLSX;*.XLS";
                if (opf.ShowDialog() == DialogResult.OK)
                {
                    string filename = opf.FileName;
                    string ConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", filename);
                    DataSet ds = new DataSet("EXCEL");
                    OleDbConnection cn = new OleDbConnection(ConStr);

                    if (cn.State == ConnectionState.Closed) cn.Open();
                    DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { });
                    string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
                    string select = String.Format("SELECT * FROM [{0}]", sheet1);
                    OleDbDataAdapter ad = new OleDbDataAdapter(select, cn);
                    ad.Fill(ds);
                    tbl_drugs.Clear();
                    tbl_drugs = ds.Tables[0];
                    cn.Close();
                    dataGridView1.DataSource = tbl_drugs.DefaultView;
                }
                if (dataGridView1.Rows.Count > 0)
                {
                    button4.Visible = true;
                }
                else
                {
                    dataGridView1.DataSource = dt_rep.DefaultView;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void DoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            //- Редактировние ячейки
            drug_code = dataGridView1.CurrentRow.Cells[0].Value.ToString();
            drug_name = dataGridView1.CurrentRow.Cells[1].Value.ToString();
            delta = dataGridView1.CurrentRow.Cells["Дельта"].Value.ToString();
            if (dataGridView1.CurrentRow.Cells["Знак дельты"].Value.ToString() == "+")
            {
                sign = 1;
            }
            else if (dataGridView1.CurrentRow.Cells["Знак дельты"].Value.ToString() == "-")
            {
                sign = 0;
            }
            Form4 form = new Form4();
            form.ShowDialog();
            dataGridView1.CurrentRow.Cells["Дельта"].Value = Convert.ToDouble(delta);
            if (sign == 1)
            {
                dataGridView1.CurrentRow.Cells["Знак дельты"].Value = "+";
            }
            else if (sign == 0)
            {
                dataGridView1.CurrentRow.Cells["Знак дельты"].Value = "-";
            }
            dataGridView1.Refresh();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            //- Внести информацию в базу
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[9];
                par[0].name = "@flag_ins";
                par[0].value = 1;
                par[1].name = "@pharmcode";
                par[1].value = null;
                par[2].name = "@flag_bit";
                par[2].value = null;
                par[3].name = "@drugcode";
                par[3].value = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value);
                par[4].name = "@delta";
                par[4].value = Convert.ToInt32(dataGridView1.Rows[i].Cells[2].Value);
                par[5].name = "@sign";
                if (dataGridView1.Rows[i].Cells[3].Value.ToString() == "-")
                {
                    par[5].value = 0;
                }
                else if (dataGridView1.Rows[i].Cells[3].Value.ToString() == "+")
                {
                    par[5].value = 1;
                }
                par[6].name = "@flag_new";
                par[6].value = 0;
                par[7].name = "@date_b";
                par[7].value = Convert.ToDateTime(dataGridView1.Rows[i].Cells[4].Value);
                par[8].name = "@date_e";
                par[8].value = Convert.ToDateTime(dataGridView1.Rows[i].Cells[5].Value);

                int rez = 0;
                rez = (new SqlStorProc()).SqlProcExecNonQuery(conn, "[pas_InsPharmDrugs]", 30, par);
                if (rez != 0)
                {
                    button4.Visible = false;
                }
                conn.Close();
                MessageBox.Show("Информация внесена в базу");
            }
        }
    }
}
